package Project_etow;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import javax.mail.Authenticator;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.Part;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.swing.JOptionPane;

import GroupClass.*;

public class EtownImpl extends UnicastRemoteObject implements EtowInterface {

    private ServerUI  ui;
    private String    url      = "jdbc:sqlserver://localhost:1433;databaseName=Etown";
    private String    userName = "sa";
    private String    password = "123456";
    private ResultSet rs       = null;
    
    public EtownImpl() throws RemoteException {
    }

    public EtownImpl(ServerUI ui) throws RemoteException {
        this.ui = ui;
    }

    public List<AccountClass> GetLogin(String username, String pass) throws RemoteException {

        List<AccountClass> ls = new ArrayList<AccountClass>();
        String accid = "False", groid = " ";
        rs = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            String sqlQuery = "Select accountid,groupid,loginName,password from Account Where loginName = '" + username
                    + "' and password ='" + pass + "'";
            rs = st.executeQuery(sqlQuery);
            if (rs.next()) {
                accid = rs.getString(1);
                groid = rs.getString(2);
                ls.add(new AccountClass(accid, groid));
            } else {
                ls.add(new AccountClass("", ""));
            }

        }

        catch (Exception e) {
        }
        ui.addItem("Call from client: " + username + ":" + pass);
        return ls;
    }

    public List<AccountClass> showlogin() throws RemoteException {

        List<AccountClass> ls = new ArrayList<AccountClass>();

        ResultSet kq = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            String sqlQuery = "Select * from Account";
            kq = st.executeQuery(sqlQuery);
            while (kq.next()) {
                int id = kq.getInt(1);
                String login = kq.getString(3);
                String name = kq.getString(5);
                String companyname = kq.getString(6);
                String age = kq.getString(7);
                String sex = kq.getString(8);
                String cmnd = kq.getString(9);
                String address = kq.getString(10);
                String phone = kq.getString(11);
                String email = kq.getString(12);
                ls.add(new AccountClass(id, login, name, companyname, age, sex, cmnd, address, phone, email));
            }
        } catch (Exception e) {
        }

        return ls;

    }

    @Override
    public String InsertAccount(int groupname, String loginname, String password1, String name, String companyname,
            String age, String sex, String cmnd, String address, String phone, String email) throws RemoteException {
        rs = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            st.execute("Insert into Account (GroupId,LoginName,[Password],Name,CompanyName,Birthday,Sex,Cmnd,[Address],Phone,Email) values ('"
                    + groupname
                    + "','"
                    + loginname
                    + "','"
                    + password1
                    + "','"
                    + name
                    + "','"
                    + companyname
                    + "','"
                    + age + "','" + sex + "','" + cmnd + "','" + address + "','" + phone + "','" + email + "')");

        } catch (Exception e) {
        }
        return "Insert";
    }

    @Override
    public String EditAccount(String name, String brithday, String cmnd, String sex, String companyname1,
            String address1, int phone1, String email1, int idacc1) throws RemoteException {
        rs = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            st.executeUpdate("UPDATE Account SET Name = '" + name + "',Birthday ='" + brithday + "',Cmnd ='" + cmnd
                    + "',Sex ='" + sex + "',  CompanyName = '" + companyname1 + "' , Address = '" + address1
                    + "', Phone = '" + phone1 + "', Email = '" + email1 + "' where AccountId = " + idacc1 + " ");

        } catch (Exception e) {
        }

        return "ThanhCong";
    }

    @Override
    public String DeleteAccount(String idacc) throws RemoteException {
        Boolean resultdelete;
        rs = null;
        String aler = "";
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            resultdelete = st.execute("DELETE FROM Account WHERE AccountId = " + idacc + " ");
            if (resultdelete) {
                aler = "Ok";
            } else {
                aler = "NotOk";
            }            
            con.close();
        } catch (Exception e) {
        }
        ui.addItem("DELETE FROM Account WHERE AccountId = " + idacc + " ");
        return aler;
    }

    @Override
    public List<UnitOfUser> showunit(String iduser) throws RemoteException {
        String nameofunit = null, floorofunit = null, billofunit = null;
        String floorname = null, townidoffloor = null;
        String townname = null;
        String statusbill = null;
        List<UnitOfUser> us = new ArrayList<UnitOfUser>();

        ResultSet kq1 = null, kq2 = null, kq3 = null, kq4 = null;
        java.sql.Connection con = null;
        try {

            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            kq1 = st.executeQuery("Select * from Unit where AccountId = " + iduser);
            while (kq1.next()) {
                nameofunit = kq1.getString(5);
                floorofunit = kq1.getString(2);
                billofunit = kq1.getString(3);
            }
            kq2 = st.executeQuery("Select * from Floor where FloorId = " + floorofunit);
            while (kq2.next()) {
                townidoffloor = kq2.getString(2);
                floorname = kq2.getString(3);
            }

            kq3 = st.executeQuery("Select * from Town where TownId = " + townidoffloor);
            while (kq3.next()) {
                townname = kq3.getString(2);
            }
            kq4 = st.executeQuery("Select * from Bill where BillId = " + billofunit);
            while (kq4.next()) {
                statusbill = kq4.getString(8);
                if (statusbill.equals(1)) {
                    statusbill = "Da Thanh Toan";
                } else {
                    statusbill = "Chua Thanh Toan";
                }
            }
            
            con.close();
            us.add(new UnitOfUser(townname, floorname, nameofunit, statusbill));
        }

        catch (Exception e) {
        }

        return us;
    }

    @Override
    public List<AccountClass> ShowMyProfiles(int accid) throws RemoteException {
        List<AccountClass> ls = new ArrayList<AccountClass>();

        ResultSet kq = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            String sqlQuery = "Select * from Account where AccountId = " + accid;

            kq = st.executeQuery(sqlQuery);
            while (kq.next()) {
                String idacc = kq.getString(1);
                String groupname = kq.getString(2);
                String login = kq.getString(3);
                String name = kq.getString(5);
                String companyname = kq.getString(6);
                String age = kq.getString(7);
                String sex = kq.getString(8);
                String cmnd = kq.getString(9);
                String address = kq.getString(10);
                String phone = kq.getString(11);
                String email = kq.getString(12);
                ls.add(new AccountClass(idacc, groupname, login, name, companyname, age, sex, cmnd, address, phone,
                        email));

            }
            kq.close();
            con.close();
            
        } catch (Exception e) {
        }

        return ls;
    }

    @Override
    public String CheckGroup(String idgroup) throws RemoteException {
        ResultSet kq = null;
        String groupname = null;
        rs = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            String sqlQuery = "Select * from GroupAccounts where GroupId = " + idgroup;
            kq = st.executeQuery(sqlQuery);
            if (kq.next()) {
                groupname = kq.getString(2);

            }
            kq.close();
            con.close();
        } catch (Exception e) {
        }

        return groupname;
    }

    @Override
    public String ChangePasswordAccount(String accid, String password1) {
        java.sql.Connection con = null;

        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            // st.executeUpdate("UPDATE Account Set Password = '1' where AccountId = '3'");
            st.executeUpdate("UPDATE Account Set Password = '" + password1 + "' where AccountId = '" + accid + "'");
            
            con.close();
        } catch (Exception e) {
        }

        return null;
    }

    @Override
    public List<TownClass> showtownfree() throws RemoteException {

        List<TownClass> twnc = new ArrayList<TownClass>();

        ResultSet kq = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            kq = st.executeQuery("Select * from Town where Status = " + 0);
            while (kq.next()) {

                String townid = kq.getString(1);
                String townname = kq.getString(2);
                String townflonumber = kq.getString(3);

                twnc.add(new TownClass(townid, townname, townflonumber, "Con Trong"));
            }
        } catch (Exception e) {
        }

        return twnc;
    }

    @Override
    public List<UnitClass> showunitfreeoftown(String floorid) throws RemoteException {
        String unitname = null, unitrid = null, unitprice = null, unitstatus = null, unitdecorate = null;

        List<UnitClass> unitcl = new ArrayList<UnitClass>();

        ResultSet kq2 = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();

            kq2 = st.executeQuery("Select * from Unit where FloorId = " + floorid);

            while (kq2.next()) {
                unitrid = kq2.getString(1);
                unitname = kq2.getString(5);
                unitprice = kq2.getString(6);
                unitstatus = kq2.getString(7);
                unitdecorate = kq2.getString(8);

                if (unitstatus.equals("0")) {
                    unitcl.add(new UnitClass(unitname, unitrid, unitprice, unitdecorate));
                }

            }

        } catch (Exception e) {
        }

        return unitcl;

    }

    @Override
    public List<FloorClass> showfloorfreeoftown(String townid) throws RemoteException {
        String floorname = null, floorid = null, floorsatus = null;
        List<FloorClass> unitcl = new ArrayList<FloorClass>();

        ResultSet kq2 = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();

            kq2 = st.executeQuery("Select * from Floor where TownId = " + townid);
            while (kq2.next()) {
                floorid = kq2.getString(1);
                floorname = kq2.getString(3);
                floorsatus = kq2.getString(4);

                if (floorsatus.equals("0")) {
                    unitcl.add(new FloorClass(floorid, floorname, "Con trong"));
                }

            }

        } catch (Exception e) {
        }

        return unitcl;
    }

    @Override
    public List<RentUnitClass> renunit(String unitid) throws RemoteException {

        List<RentUnitClass> renunit = new ArrayList<RentUnitClass>();
        String unitname = null, unitprice = null, unitdecorate = null, unitidbill = null;
        String billpayid = null;
        String Securit = null, WashingRooms = null, CleanBuildingGlasses = null, MaintenanceElevator = null, AmountOfParking = null;
        ResultSet kq1 = null, kq2 = null, kq3 = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            kq1 = st.executeQuery("Select * from Unit where UnitId = " + unitid);
            while (kq1.next()) {
                unitidbill = kq1.getString(3);
                unitname = kq1.getString(5);
                unitprice = kq1.getString(6);

                unitdecorate = kq1.getString(8);
            }

            kq2 = st.executeQuery("Select * from Bill where BillId = " + unitidbill);
            while (kq2.next()) {
                billpayid = kq2.getString(3);
            }

            kq3 = st.executeQuery("Select * from BillPaymentOfService where BillPaymentID = " + billpayid);
            while (kq3.next()) {

                Securit = kq3.getString(2);
                WashingRooms = kq3.getString(3);
                CleanBuildingGlasses = kq3.getString(4);
                MaintenanceElevator = kq3.getString(5);
                AmountOfParking = kq3.getString(6);
            }

            renunit.add(new RentUnitClass(unitname, unitprice, unitdecorate, Securit, WashingRooms,
                    CleanBuildingGlasses, MaintenanceElevator, AmountOfParking));

        } catch (Exception e) {
        }

        return renunit;
    }

    @Override
    public List<BillClass> bill() throws RemoteException {
        ResultSet kq = null;
        java.sql.Connection con = null;
        List<BillClass> ls = new ArrayList<BillClass>();

        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            String sqlQuery = "SELECT Name, Companyname,Email,UnitName,UnitPrice,[Power],Water,[Security],WashingRooms,CleanBuildingGlasses,MaintenanceElevator,AmountOfParking,DateOfPayment,Bill.[Status],Bill.BillId,Bill.BillPaymentID FROM Unit,BillPaymentOfService, Account,Bill WHERE Account.AccountID = Bill.AccountID AND Bill.BillId = Unit.BillId AND BillPaymentOfService.BillPaymentID = Bill.BillId";
            kq = st.executeQuery(sqlQuery);

            while (kq.next()) {
                String name = kq.getString(1);
                String companyname = kq.getString(2);
                String email = kq.getString(3);
                String unitname = kq.getString(4);
                String unitprice = kq.getString(5);
                String power = kq.getString(6);
                String water = kq.getString(7);
                String security = kq.getString(8);
                String washing = kq.getString(9);
                String clean = kq.getString(10);
                String main = kq.getString(11);
                String amount = kq.getString(12);
                String datepaymen = kq.getString(13);
                int billid = kq.getInt(15);
                int billpayment = kq.getInt(16);
                String statusbill = kq.getString(14);
                ls.add(new BillClass(name,companyname,email,unitname,unitprice,power,water,security,washing,clean,main,amount,datepaymen, statusbill, billid, billpayment));
            }
        } catch (Exception e) {
        }
        return ls;
    }

    @Override
    public String UpdateRentUnit(String unitid, String accid, String unitstatus, String unitdeco, String unitday)
            throws RemoteException {
        String getidbill = null, chua = null;
        Statement st = null;
        Calendar currentDate = Calendar.getInstance();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
        String dateNow = formatter.format(currentDate.getTime());

        ResultSet kq1 = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            st = con.createStatement();

            st.executeQuery("INSERT INTO Bill VALUES ( '" + accid + "', 1, 0, 0, '" + dateNow + "' , 1)");
        } catch (Exception e) {
        }

        try {
            kq1 = st.executeQuery("SELECT  * From Bill");
            while (kq1.next()) {
                getidbill = kq1.getString(1);
                chua = getidbill;
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }

        try {
            st.executeUpdate("UPDATE Unit SET AccountId = '" + accid + "' , BillId = '" + chua
                    + "' ,[Status] = '1', Decorate = '" + unitdeco + "' where UnitId = " + unitid + " ");
        } catch (SQLException e) {

            e.printStackTrace();
        }

        return "ThanhCong";
    }

    @Override
    public String Edit(String name1, String companyname1, String sex1, String brithday1, String address1, int phone1,String email1, int idedit) throws RemoteException {
      
        rs = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            st.executeUpdate("UPDATE Account SET Name = '" + name1 + "',CompanyName = '" + companyname1 + "',[Birthday] = '" + brithday1 + "',Sex ='" + sex1 + "', [Address]  = '" + address1+ "', Phone = '0" + phone1 + "', Email = '" + email1 + "' where AccountId = " + idedit + " ");

        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        ui.addItem("UPDATE Account SET Name = '" + name1 + "',CompanyName = '" + companyname1 + "',[Birthday] = '" + brithday1 + "',Sex ='" + sex1 + "', [Address]  = '" + address1+ "', Phone = '0" + phone1 + "', Email = '" + email1 + "' where AccountId = " + idedit + " ");
        return "ok";
    }

    @Override
    public String send(String to , String body)throws RemoteException {
        
        Properties props = System.getProperties();
        props.put("mail.smtp.host", "smtp.gmail.com");
        props.put("mail.smtp.port", "587");
        props.put("mail.smtp.starttls.enable", "true");
        final String login = "toanlamchi@gmail.com";
        final String pwd = "08119800";
        Authenticator pa = null;
        if (login != null && pwd != null) { 
            props.put("mail.smtp.auth", "true");
            pa = new Authenticator() {

                public PasswordAuthentication getPasswordAuthentication() {
                    return new PasswordAuthentication(login, pwd);
                }
            };
        }
        Session session = Session.getInstance(props, pa);

        Message msg = new MimeMessage(session);
        MimeBodyPart mbp2 = new MimeBodyPart();

        try {
            msg.setFrom(new InternetAddress(login));
            msg.setRecipients(Message.RecipientType.TO, InternetAddress.parse(
                    to, false));

            msg.setSubject("Send Mail Insert");
            msg.setText(body);

            msg.setHeader("X-Mailer", "LOTONtechEmail");
            msg.setSentDate(new Date());
            msg.saveChanges();
            Transport.send(msg);
            System.out.println("Mail da duoc gui");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Mail Khong Goi Duoc");
            e.getMessage();
        }
        return null;
    }

    @Override
    public String updatestatus(String id , String dateof) throws RemoteException {
        rs = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            st.executeUpdate("UPDATE Bill SET Status = ' 1', DateOfPayment = '" + dateof + "' where BillId = " + id + " ");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return null;
    }

    @Override
    public String updatedate(String id ,String dateof) throws RemoteException {
        rs = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            st.executeUpdate("UPDATE Bill SET DateOfPayment = '" + dateof + "' where BillId = " + id + " ");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return null;
    }

    @Override
    public String SendAndAttachFile(String to, String body) throws RemoteException {
        try {
            // create a message
            Properties props = System.getProperties();
            props.put("mail.smtp.host", "smtp.gmail.com");
            props.put("mail.smtp.port", "587");
            props.put("mail.smtp.starttls.enable", "true");
            final String login = "toanlamchi";
            final String pwd = "08119800";
            Authenticator pa = null;
            if (login != null && pwd != null) { 
                props.put("mail.smtp.auth", "true");
                pa = new Authenticator() {

                    public PasswordAuthentication getPasswordAuthentication() {
                        return new PasswordAuthentication(login, pwd);
                    }
                };
            }
            
            Session session = Session.getInstance(props, pa);
            MimeMessage msg = new MimeMessage(session);
            msg.setFrom(new InternetAddress("toanlamchi@gmail.com"));
            InternetAddress[] address = {new InternetAddress(to)};
            msg.setRecipients(Message.RecipientType.TO, address);
            msg.setSubject("Tá»•ng Chi PhÃ­ Pháº£i Thanh ToÃ¡n Ä�Æ°á»£c Gá»Ÿi KÃ¨m Theo File PDF");

            // create and fill the first message part
            MimeBodyPart mbp1 = new MimeBodyPart();
            mbp1.setText(body);

            // create the second message part
            MimeBodyPart mbp2 = new MimeBodyPart();

            // attach the file to the message
            mbp2.attachFile("E:\\Thu Tien toan.pdf");


            // create the Multipart and add its parts to it
            Multipart mp = new MimeMultipart();
            mp.addBodyPart(mbp1);
            mp.addBodyPart(mbp2);

            // add the Multipart to the message
            msg.setContent(mp);

            // set the Date: header
            msg.setSentDate(new Date());

            // send the message
            Transport.send(msg);
            JOptionPane.showMessageDialog(null, "Mail Sent Successfully");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Mail Not Sent Successfully");
            e.getMessage();
        }
        return "Submit";
    }
	
		@Override
	public List<BillPaymentClass> ShowAllBillPayment() throws RemoteException {
		
		List<BillPaymentClass> ls = new ArrayList<BillPaymentClass>();
		
		ResultSet kq = null;
		java.sql.Connection con = null;
		try{	
			con = DriverManager.getConnection (url,userName,password);
			Statement st = con.createStatement();		
		    String sqlQuery = "Select * from BillPaymentOfService";
	        kq = st.executeQuery(sqlQuery);
	        while(kq.next()){
	        	String billpayid = kq.getString(1);
	        	String billpaysec = kq.getString(2);
	        	String billpaywas = kq.getString(3);
	        	String billpayclean = kq.getString(4);
	        	String billpaymaint = kq.getString(5);
	        	String billpaypark = kq.getString(6);
	        	
	        	ls.add(new BillPaymentClass(billpaysec, billpaywas, billpayclean, billpaymaint, billpaypark,billpayid));
	        	
	        }	        
	        }
        catch (Exception e) {		
		}		
        
		return ls;
	}

    @Override
        public String InsertBillPayment(String billPaymetSecurity , String billPaymetWashingRooms ,
            String billPaymetCleanBuildingGlasses , String billPaymetMaintenanceElevator ,
            String billPaymetAmountOfParking) throws RemoteException
    {
        rs = null;
        java.sql.Connection con = null;
        try {
                con = DriverManager.getConnection(url, userName, password);
                Statement st = con.createStatement();
                st.execute("Insert into BillPaymentOfService ([Security],[WashingRooms],[CleanBuildingGlasses],[MaintenanceElevator],[AmountOfParking]) values ('"
                                + billPaymetSecurity
                                + "','"
                                + billPaymetWashingRooms
                                + "','"
                                + billPaymetCleanBuildingGlasses
                                + "','"
                                + billPaymetMaintenanceElevator
                                + "','"
                                + billPaymetAmountOfParking
                                + "')");

        } catch (Exception e) {
        }
        return "InsertSusscess";
    }

    @Override
    public String UpdateBillPayment(String billPaymetSecurity , String billPaymetWashingRooms ,
            String billPaymetCleanBuildingGlasses , String billPaymetMaintenanceElevator ,
            String billPaymetAmountOfParking , String IdbillPaymet) throws RemoteException
    {

        java.sql.Connection con = null;
        try {
                con = DriverManager.getConnection(url, userName, password);
                Statement st = con.createStatement();
                st.executeUpdate("UPDATE BillPaymentOfService SET [Security] = '"+ billPaymetSecurity + "',[WashingRooms] ='"+ billPaymetWashingRooms + "',[CleanBuildingGlasses] ='"+ billPaymetCleanBuildingGlasses + "',[MaintenanceElevator] ='"+ billPaymetMaintenanceElevator + "',  [AmountOfParking] = '"+ billPaymetAmountOfParking + "' where BillPaymentID = " + IdbillPaymet + " ");

        } catch (Exception e) {
        }
        
        return "ThanhCong";
        
    }


    @Override
    public String DeleteBillPayment(String idbill) throws RemoteException
    {
        Boolean resultdelete;
        rs = null;
        String aler = "";
        java.sql.Connection con = null;
        try {
                con = DriverManager.getConnection(url, userName, password);
                Statement st = con.createStatement();
                resultdelete = st.execute("DELETE FROM BillPaymentOfService WHERE BillPaymentID = " + idbill +" ");
                if(resultdelete)
                {
                        aler = "Ok";
                }
                else
                {
                        aler = "NotOk";
                }
                con.close();
        } catch (Exception e) {
        }
        return aler;
    }
	
	@Override
	public String InsertTown(String nametown, String numfloortown,
			String statustown) throws RemoteException{
		rs = null;
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			st.execute("Insert into Town (TownName,NumberFloor,[Status]) values ('"
					+ nametown
					+ "','"
					+ numfloortown
					+ "','"
					+ statustown + "')");

		} catch (Exception e) {
		}
		ui.addItem("Insert into Town (TownName,NumberFloor,[Status]) values ('"
				+ nametown
				+ "','"
				+ numfloortown
				+ "','"
				+ statustown + "')");

		return "Insert";
	}

	@Override
	public String EditTown(String nametown, int numfloortown, int id,String status)
			throws RemoteException {
		rs = null;
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			st.executeUpdate("UPDATE Town SET TownName = '"+ nametown + "',NumberFloor = "+ numfloortown + ",Status = "+ status +" where TownId = " + id + " ");

		} catch (Exception e) {
		}
		
		return "ThanhCong";
	}

	@Override
	public String DeleteTown(String idTown) {
		Boolean resultdelete;
		rs = null;
		String aler = "";
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			resultdelete = st.execute("DELETE FROM Town WHERE TownId = " + idTown +" ");
			if(resultdelete)
			{
				aler = "Ok";
			}
			else
			{
				aler = "NotOk";
			}
			con.close();
		} catch (Exception e) {
		}
		ui.addItem("DELETE FROM Town WHERE TownId = " + idTown +" ");
		return aler;
	}

	@Override
	public String InsertFloor(int townID, String floorName, String statusFloor)
			throws RemoteException {
		rs = null;
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			st.execute("Insert into [Floor] (TownId,FloorName,[Status]) values ("
					+ townID
					+ ",'"
					+ floorName
					+ "','"
					+ statusFloor + "')");

		} catch (Exception e) {
		}
		ui.addItem("Insert into [Floor] (TownId,FloorName,[Status]) values ("
				+ townID
				+ ",'"
				+ floorName
				+ "','"
				+ statusFloor + "')");

		return "Insert";
	}

	@Override
	public String EditFloor(String floorName, String status, int floorId)
			throws RemoteException {
		rs = null;
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			st.executeUpdate("UPDATE [Floor] SET FloorName = '"+ floorName + "',[Status] = "+ status + " where FloorId = " + floorId + " ");

		} catch (Exception e) {
		}
		
		return "ThanhCong";
	}

	@Override
	public String DeleteFloor(String idFloor) throws RemoteException {
		Boolean resultdelete;
		rs = null;
		String aler = "";
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			resultdelete = st.execute("DELETE FROM [Floor] WHERE FloorId = " + idFloor +" ");
			if(resultdelete)
			{
				aler = "Ok";
			}
			else
			{
				aler = "NotOk";
			}
			con.close();
		} catch (Exception e) {
		}
		ui.addItem("DELETE FROM [Floor] WHERE FLoorId = " + idFloor +" ");
		return aler;
	}

	@Override
	public String InsertUnit(int floorID, String unitName, String unitPrice, String decorate, String billId)
			throws RemoteException {
		rs = null;
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			st.execute("Insert into Unit (FloorId,UnitName,UnitPrice,Decorate,[Status],BillId) values ("
					+ floorID
					+ ",'"
					+ unitName
					+ "',"
					+ unitPrice
					+ ","
					+ decorate
					+ ","
					+ 0 
					+ ","
					+ billId + ")");

		} catch (Exception e) {
		}
		ui.addItem("Insert into Unit (FloorId,UnitName,UnitPrice,Decorate,[Status]) values ("
				+ floorID
				+ ",'"
				+ unitPrice
				+ ","
				+ decorate
				+ ","
				+ 0 
				+ ","
				+ billId + ")");

		return "Insert";
	}

	@Override
	public String EditUnit(String unitName, String unitPrice, String decorate,
			int unitID) throws RemoteException {
		rs = null;
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			st.executeUpdate("UPDATE Unit SET UnitName = '"+ unitName + "',UnitPrice = " + unitPrice  + " where UnitId = " + unitID + " ");

		} catch (Exception e) {
		}
		
		return "ThanhCong";
	}

	@Override
	public String DeleteUnit(String idUnit) throws RemoteException {
		Boolean resultdelete;
		rs = null;
		String aler = "";
		java.sql.Connection con = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			Statement st = con.createStatement();
			resultdelete = st.execute("DELETE FROM Unit WHERE UnitId = " + idUnit +" ");
			if(resultdelete)
			{
				aler = "Ok";
			}
			else
			{
				aler = "NotOk";
			}
			con.close();
		} catch (Exception e) {
		}
		ui.addItem("DELETE FROM Unit WHERE UnitId = " + idUnit +" ");
		return aler;
	}

    @Override
    public String updatebill(String id,String idpayment ,String unitprice, String power, String water,String dateofpayment, String security, String washingroom,
            String clenabuildingg, String maintenan, String amountofparking) throws RemoteException {
        rs = null;
        java.sql.Connection con = null;
        try {
            con = DriverManager.getConnection(url, userName, password);
            Statement st = con.createStatement();
            st.executeUpdate("UPDATE Bill SET UnitPrice = '"+ unitprice + "',[Power] = '" + power  + "' ,[Water] = '" + water  + "',[DateOfPayment] = '" + dateofpayment  + "' where BillId = " + id );
            st.executeUpdate("UPDATE BillPaymentOfService SET [Security] = '"+ security + "',WashingRooms = '" + washingroom  + "',CleanBuildingGlasses = '" + clenabuildingg  + "' ,MaintenanceElevator = '" + maintenan  + "',AmountOfParking = '" + amountofparking  + "'   where BillPaymentID = " + idpayment );
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }            
        return "ok";
    }
}
